package cn.hfatec.shms.web.vip.vipXxb.action;

import cn.hfatec.shms.advice.annotation.MysqlDataSource;
import cn.hfatec.shms.cache.CodeInfoCache;
import cn.hfatec.shms.constants.ShmsConstant;
import cn.hfatec.shms.constants.ShmsProperties;
import cn.hfatec.shms.enums.CodeType;
import cn.hfatec.shms.exception.NotAuthException;
import cn.hfatec.shms.system.codeInfo.model.CodeInfo;
import cn.hfatec.shms.system.organization.model.Organization;
import cn.hfatec.shms.system.user.model.User;
import cn.hfatec.shms.utils.ExcelUtil;
import cn.hfatec.shms.web.members.cardType.model.CardType;
import cn.hfatec.shms.web.members.ykManage.model.YkManageView;
import cn.hfatec.shms.web.td.tdHzb.model.TdHzbParams;
import cn.hfatec.shms.web.vip.vipXxb.model.VipCheck;
import cn.hfatec.shms.web.yk.ykHsb.model.YkHsb;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
import javax.annotation.Resource;


import cn.hfatec.JsonResponse;
import cn.hfatec.Page;
import cn.hfatec.Table;
import cn.hfatec.constants.SysContant;
import cn.hfatec.freemarker.FreeMarkerUtil;
import cn.hfatec.shms.web.vip.vipXxb.model.VipXxbParams;
import cn.hfatec.shms.web.vip.vipXxb.model.VipXxb;
import cn.hfatec.shms.web.vip.vipXxb.service.VipXxbService;
import cn.hfatec.shms.base.ShmsBaseAction;
import org.springframework.ui.Model;

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;

import org.springframework.validation.BindingResult;
import org.springframework.validation.ObjectError;


/**
 * @author hfatec codegen
 * @version 1.0
 *          <p/>
 *          the JAVA code is generate by middlegen
 */
@Controller
@RequestMapping("/vip/vipXxb")
public class VipXxbAction extends ShmsBaseAction {

    //服务层
    @Resource(name = "VipXxbService")
    private VipXxbService vipXxbService;
    @Resource(name = "shmsProperties")
    private ShmsProperties shmsProperties;

    public static String encodeFilename(HttpServletRequest request, String fileName) {
        String rtn = "";
        try {
            String userAgent = request.getHeader("USER-AGENT");
            String new_filename = URLEncoder.encode(fileName, "UTF8");

            rtn = "filename=\"" + new_filename + "\"";

            if (userAgent != null) {
                userAgent = userAgent.toLowerCase();

                if (userAgent.indexOf("msie") != -1) {
                    rtn = "filename=\"" + new_filename + "\"";
                } else if (userAgent.indexOf("opera") != -1) {
                    rtn = "filename*=UTF-8''" + new_filename;
                } else if (userAgent.indexOf("safari") != -1) {
                    rtn = "filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + "\"";
                } else if (userAgent.indexOf("applewebkit") != -1) {
                    new_filename = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
                    rtn = "filename=\"" + new_filename + "\"";
                } else if (userAgent.indexOf("mozilla") != -1) {
                    rtn = "filename*=UTF-8''" + new_filename;
                }
            }
        } catch (UnsupportedEncodingException e) {
            try {
                rtn = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
            } catch (UnsupportedEncodingException localUnsupportedEncodingException1) {
            }
        }
        return rtn;
    }

    /**
     * 查询列表
     */
    @RequestMapping("/list")
    public String list(VipXxbParams vipXxbParams, Page page, Model model) {

        Table table = vipXxbService.queryVipXxbTable(vipXxbParams, page);
        model.addAttribute("vipXxbsTable", table);
        model.addAttribute("vipXxbParams", vipXxbParams);
        model.addAttribute("cardList", CodeInfoCache.getCodeInfo(CodeType.CARD_TYPE));
        model.addAttribute("boxList", CodeInfoCache.getCodeInfo(CodeType.BOX_TYPE));
        model.addAttribute("coopeateList", CodeInfoCache.getCodeInfo(CodeType.COOPEATE_DEGREE));
        model.addAttribute("interventionList", CodeInfoCache.getCodeInfo(CodeType.INTERVENTION_TYPE));
        model.addAttribute("feelList", CodeInfoCache.getCodeInfo(CodeType.FEEL_TYPE));
        model.addAttribute("dosageList", CodeInfoCache.getCodeInfo(CodeType.DOSAGE_SIZE));
        model.addAttribute("vipList", CodeInfoCache.getCodeInfo(CodeType.VIP_RAND));
        model.addAttribute("contactList", CodeInfoCache.getCodeInfo(CodeType.CONTACT_TIME_TYPE));
        model.addAttribute("problemList", CodeInfoCache.getCodeInfo(CodeType.PROBLEM_TYPE));
        model.addAttribute("sexList", CodeInfoCache.getCodeInfo(CodeType.SEX));
        model.addAttribute("sfgqList", CodeInfoCache.getCodeInfo(CodeType.YES_OR_NO));
        return "/vip/vipXxb/list";
    }

    /**
     * 异步查询列表
     */
    @RequestMapping("/ajaxList")
    @ResponseBody
    public String ajaxList(VipXxbParams vipXxbParams, Page page, Model model) {
        try {
            list(vipXxbParams, page, model);
            return new JsonResponse<Map>(JsonResponse.RETURN_SUCCESS, "", FreeMarkerUtil.render("/vip/vipXxb/table", model)).toString();

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return JsonResponse.error;
    }

    /**
     * 异步查询列表
     */
    @RequestMapping("/ajaxXhList")
    @ResponseBody
    public String ajaxXhList(VipXxbParams vipXxbParams, Page page, Model model) {
        try {
            list(vipXxbParams, page, model);
            return new JsonResponse<Map>(JsonResponse.RETURN_SUCCESS, "", FreeMarkerUtil.render("/vip/vipXxb/tableXh", model)).toString();

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return JsonResponse.error;
    }

    /**
     * 异步查询列表
     */
    @RequestMapping("/ajaxGlxgList")
    @ResponseBody
    public String ajaxGlxgList(VipXxbParams vipXxbParams, Page page, Model model) {
        try {
            list(vipXxbParams, page, model);
            return new JsonResponse<Map>(JsonResponse.RETURN_SUCCESS, "", FreeMarkerUtil.render("/vip/vipXxb/tableGlxg", model)).toString();

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return JsonResponse.error;
    }

    /**
     * 跳转到新增页
     */
    @RequestMapping("/goAdd")
    @ResponseBody
    public String goAdd(Model model) {
        try {
            VipXxb vipXxb = new VipXxb();
            model.addAttribute("vipXxb", vipXxb);
            model.addAttribute("cardList", CodeInfoCache.getCodeInfo(CodeType.CARD_TYPE));
            model.addAttribute("boxList", CodeInfoCache.getCodeInfo(CodeType.BOX_TYPE));
            model.addAttribute("coopeateList", CodeInfoCache.getCodeInfo(CodeType.COOPEATE_DEGREE));
            model.addAttribute("interventionList", CodeInfoCache.getCodeInfo(CodeType.INTERVENTION_TYPE));
            model.addAttribute("feelList", CodeInfoCache.getCodeInfo(CodeType.FEEL_TYPE));
            model.addAttribute("dosageList", CodeInfoCache.getCodeInfo(CodeType.DOSAGE_SIZE));
            model.addAttribute("vipList", CodeInfoCache.getCodeInfo(CodeType.VIP_RAND));
            model.addAttribute("contactList", CodeInfoCache.getCodeInfo(CodeType.CONTACT_TIME_TYPE));
            model.addAttribute("problemList", CodeInfoCache.getCodeInfo(CodeType.PROBLEM_TYPE));
            model.addAttribute("sexList", CodeInfoCache.getCodeInfo(CodeType.SEX));
            model.addAttribute("sfgqList", CodeInfoCache.getCodeInfo(CodeType.YES_OR_NO));
            return new JsonResponse<Map>(JsonResponse.RETURN_SUCCESS, "", FreeMarkerUtil.render("/vip/vipXxb/form", model)).toString();

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return JsonResponse.error;
    }

    /**
     * 跳转到编辑页
     */
    @RequestMapping("/goEdit")
    @ResponseBody
    public String goEdit(Integer id, Model model) {

        try {

            VipXxb vipXxb = vipXxbService.getVipXxb(id);
            if (vipXxb != null) {
                VipXxb vipXxbGx = vipXxbService.getVipCardno(vipXxb.getCardno(), vipXxb.getPackageName());
                if (vipXxbGx != null) {
                    vipXxb.setLastReport(vipXxbGx.getLastReport());
                    vipXxb.setLastDate(vipXxbGx.getLastDate());
                }
            }
            model.addAttribute("vipXxb", vipXxb);
            model.addAttribute("cardList", CodeInfoCache.getCodeInfo(CodeType.CARD_TYPE));
            model.addAttribute("boxList", CodeInfoCache.getCodeInfo(CodeType.BOX_TYPE));
            model.addAttribute("coopeateList", CodeInfoCache.getCodeInfo(CodeType.COOPEATE_DEGREE));
            model.addAttribute("interventionList", CodeInfoCache.getCodeInfo(CodeType.INTERVENTION_TYPE));
            model.addAttribute("feelList", CodeInfoCache.getCodeInfo(CodeType.FEEL_TYPE));
            model.addAttribute("dosageList", CodeInfoCache.getCodeInfo(CodeType.DOSAGE_SIZE));
            model.addAttribute("vipList", CodeInfoCache.getCodeInfo(CodeType.VIP_RAND));
            model.addAttribute("contactList", CodeInfoCache.getCodeInfo(CodeType.CONTACT_TIME_TYPE));
            model.addAttribute("problemList", CodeInfoCache.getCodeInfo(CodeType.PROBLEM_TYPE));
            model.addAttribute("sexList", CodeInfoCache.getCodeInfo(CodeType.SEX));
            model.addAttribute("sfgqList", CodeInfoCache.getCodeInfo(CodeType.YES_OR_NO));
            return new JsonResponse<Map>(JsonResponse.RETURN_SUCCESS, "", FreeMarkerUtil.render("/vip/vipXxb/form", model)).toString();

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return JsonResponse.error;
    }

    /**
     * 根据IDs删除数据
     */
    @RequestMapping("/delete")
    @ResponseBody
    public String deleteVipXxb(String ids) {
        try {
            List<String> idList = new ArrayList<String>();
            if (ids.length() > 0) {
                idList = Arrays.asList(ids.split(","));
            }
            vipXxbService.deleteVipXxbBatch(idList);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("数据" + ids + "删除异常！");
            return new JsonResponse<String>(JsonResponse.RETURN_FAILURE, SysContant.DELETE_ERROR_MESSAGE).toString();
        }
        return new JsonResponse(JsonResponse.RETURN_SUCCESS, SysContant.DELETE_SUCCESS_MESSAGE).toString();
    }

    /**
     * 保存数据
     */
    @RequestMapping("/save")
    @ResponseBody
    public String saveVipXxb(@Valid VipXxb vipXxb, BindingResult result, HttpServletRequest request) {

        try {
            User user = (User) request.getSession().getAttribute(SysContant.LOGIN_USER_SESSION);
            if (user == null) {
                throw new NotAuthException("用户未授权");
            }
            if (result.hasErrors()) {
                List<ObjectError> errors = result.getAllErrors();
                return new JsonResponse<String>(JsonResponse.RETURN_FAILURE, errors.get(0).getDefaultMessage()).toString();
            }
            vipXxbService.saveVipXxb(vipXxb, user);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("数据VipXxb保存异常！");
            return new JsonResponse<String>(JsonResponse.RETURN_FAILURE, SysContant.SAVE_ERROR_MESSAGE).toString();
        }
        return new JsonResponse<String>(JsonResponse.RETURN_SUCCESS, SysContant.SAVE_SUCCESS_MESSAGE).toString();
    }

    /**
     * 跳转到编辑页
     */
    @RequestMapping("/goFind")
    @ResponseBody
    public String goFind(String cardno, String packageName, Model model) {
        try {
            VipXxb vipXxb = vipXxbService.getVipCardno(cardno, packageName);
            VipXxb vipXxbSum = vipXxbService.getVipSum(cardno);
            VipXxb vipXxbAmb = vipXxbService.getVipAmb(cardno);
            String firstReport = null;
            if (vipXxb != null) {
                firstReport = vipXxb.getFirstReport();
            } else {
                vipXxb = new VipXxb();
            }
            vipXxb.setBox("1,2,3,4,5,6");
            vipXxb.setObligateA("0");
            VipXxb vipXxbTj = vipXxbService.getVipFirstReport(firstReport);
            if (vipXxb != null) {
            } else {
                vipXxb = new VipXxb();
            }
            if (vipXxbSum != null) {
                vipXxb.setObligateD(vipXxbSum.getObligateD());
            }
            if (vipXxbAmb != null) {
                vipXxb.setPromoteDoctor(vipXxbAmb.getPromoteDoctor());
                vipXxb.setAmbId(vipXxbAmb.getAmbId());
                vipXxb.setVipId(vipXxbAmb.getVipId());
            }

            if (vipXxbTj == null) {
                firstReport = vipXxb.getObligateB();
                vipXxbTj = vipXxbService.getVipFirstReport(firstReport);
                if (vipXxbTj != null) {
                    vipXxb.setMzId(vipXxbTj.getMzId());
                    vipXxb.setTjId(vipXxbTj.getTjId());
                    vipXxb.setProblem(vipXxbTj.getProblem());
                    vipXxb.setTjDate(vipXxbTj.getTjDate());
                }
            } else {
                vipXxb.setMzId(vipXxbTj.getMzId());
                vipXxb.setTjId(vipXxbTj.getTjId());
                vipXxb.setProblem(vipXxbTj.getProblem());
            }
            model.addAttribute("cardList", CodeInfoCache.getCodeInfo(CodeType.CARD_TYPE));
            model.addAttribute("boxList", CodeInfoCache.getCodeInfo(CodeType.BOX_TYPE));
            model.addAttribute("coopeateList", CodeInfoCache.getCodeInfo(CodeType.COOPEATE_DEGREE));
            model.addAttribute("interventionList", CodeInfoCache.getCodeInfo(CodeType.INTERVENTION_TYPE));
            model.addAttribute("feelList", CodeInfoCache.getCodeInfo(CodeType.FEEL_TYPE));
            model.addAttribute("dosageList", CodeInfoCache.getCodeInfo(CodeType.DOSAGE_SIZE));
            model.addAttribute("vipList", CodeInfoCache.getCodeInfo(CodeType.VIP_RAND));
            model.addAttribute("contactList", CodeInfoCache.getCodeInfo(CodeType.CONTACT_TIME_TYPE));
            model.addAttribute("problemList", CodeInfoCache.getCodeInfo(CodeType.PROBLEM_TYPE));
            model.addAttribute("vipXxb", vipXxb);
            model.addAttribute("sexList", CodeInfoCache.getCodeInfo(CodeType.SEX));
            model.addAttribute("sfgqList", CodeInfoCache.getCodeInfo(CodeType.YES_OR_NO));
            return new JsonResponse<Map>(JsonResponse.RETURN_SUCCESS, "", FreeMarkerUtil.render("/vip/vipXxb/vipXxb", model)).toString();

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return JsonResponse.error;
    }

    /**
     * 跳转到编辑页
     */
    @RequestMapping("/goChoose")
    @ResponseBody
    public String goChoose(String cardno) {
        try {
            VipXxb vipXxb = vipXxbService.getVipSum(cardno);
            if (vipXxb != null) {
                return new JsonResponse(JsonResponse.RETURN_SUCCESS, "", vipXxb).toString();
            } else {
                return new JsonResponse(JsonResponse.RETURN_SUCCESS, "", new VipXxb()).toString();
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return JsonResponse.error;
    }

    /**
     * 跳转到客户视图
     */
    @RequestMapping("/goView/{id}")
    public String goView(@PathVariable Integer id, Model model) {
        VipXxb vipXxb = vipXxbService.getVipXxb(id);
        model.addAttribute("vipXxb", vipXxb);
        model.addAttribute("cardList", CodeInfoCache.getCodeInfo(CodeType.CARD_TYPE));
        model.addAttribute("boxList", CodeInfoCache.getCodeInfo(CodeType.BOX_TYPE));
        model.addAttribute("coopeateList", CodeInfoCache.getCodeInfo(CodeType.COOPEATE_DEGREE));
        model.addAttribute("interventionList", CodeInfoCache.getCodeInfo(CodeType.INTERVENTION_TYPE));
        model.addAttribute("feelList", CodeInfoCache.getCodeInfo(CodeType.FEEL_TYPE));
        model.addAttribute("dosageList", CodeInfoCache.getCodeInfo(CodeType.DOSAGE_SIZE));
        model.addAttribute("vipList", CodeInfoCache.getCodeInfo(CodeType.VIP_RAND));
        model.addAttribute("contactList", CodeInfoCache.getCodeInfo(CodeType.CONTACT_TIME_TYPE));
        model.addAttribute("problemList", CodeInfoCache.getCodeInfo(CodeType.PROBLEM_TYPE));
        model.addAttribute("sexList", CodeInfoCache.getCodeInfo(CodeType.SEX));
        model.addAttribute("sfgqList", CodeInfoCache.getCodeInfo(CodeType.YES_OR_NO));
        return "/vip/vipXxb/view";
    }

    /**
     * 跳转到编辑页
     */
    @RequestMapping("/goCheck")
    @ResponseBody
    public String goCheck( ) {
        try {

            List<VipXxb> vipXxb = vipXxbService.listVipXxbTable();
            for(VipXxb vipXxbUpdate : vipXxb) {//其内部实质上还是调用了迭代器遍历方式，这种循环方式还有其他限制，不建议使用。
                VipXxb vipXxbOracle=vipXxbService.getVipUpdate(vipXxbUpdate.getCardno(),vipXxbUpdate.getFgrDoctor());
                if(vipXxbOracle!=null){
                vipXxbUpdate.setMeetTime(vipXxbOracle.getMeetTime());
                    vipXxbUpdate.setMeetType(vipXxbOracle.getMeetType());
                }
                vipXxbService.updateVipXxb(vipXxbUpdate);
            }

            if (vipXxb != null) {
                return new JsonResponse(JsonResponse.RETURN_SUCCESS, "", vipXxb).toString();
            } else {
                return new JsonResponse(JsonResponse.RETURN_SUCCESS, "", new YkHsb()).toString();
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return JsonResponse.error;
    }

    /**
     * 导入潜在客户
     *
     * @param path
     * @param request
     * @param response
     * @return
     */
    @RequestMapping("/importVip")
    @ResponseBody
    public String importVip(String path, HttpServletRequest request, HttpServletResponse response) {

        try {

            return importCust(path, request, response);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("数据Custom保存异常！");
            return new JsonResponse<String>(JsonResponse.RETURN_FAILURE, SysContant.SAVE_ERROR_MESSAGE).toString();
        }
    }

    /**
     * 导入模板下载
     *
     * @param request
     * @param response
     * @param serverFileName
     * @param clientFileName
     * @return
     */
    @RequestMapping("/downloadTemplate")
    @ResponseBody
    public String downloadTemplate(HttpServletRequest request, HttpServletResponse response, String serverFileName, String clientFileName) {
        FileInputStream fileInputStream = null;
        String msg = "";
        try {

            File file = new File(request.getSession().getServletContext().getRealPath("") + File.separator + "WEB-INF" + File.separator + "content" + File.separator + "vip" + File.separator + "vipXxb" + File.separator + serverFileName);

            long fileLength = file.length();

            // 设置下载参数
            clientFileName = encodeFilename(request, clientFileName);
            response.reset();
            response.setHeader("Content-Disposition", "attachment; " + clientFileName);
            response.addHeader("Content-Length", "" + fileLength);
            response.setContentType("application/octet-stream;charset=UTF-8");
            // response.setContentType("application/x-msdownload");

            fileInputStream = new FileInputStream(file);
            OutputStream out = response.getOutputStream();

            // 读文件
            int iBufferSize = 65000;
            byte[] buffer = new byte[65000];
            long lReadLen = 0;
            while (lReadLen < fileLength) {
                int lRead = fileInputStream.read(buffer, 0, iBufferSize);
                lReadLen += lRead;
                out.write(buffer, 0, lRead);
            }
            out.flush();

        } catch (Exception e) {
            logger.error("下载文件失败!", e);
            msg = "下载文件失败!<a href='#' onclick='javascript:history.go(-1);'>返回</a><br/>";
            msg += e.getMessage();
        } finally {
            if (fileInputStream != null) {
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                }
            }
        }

        return msg;
    }

    /**
     * 导入天道数据
     *
     * @param path
     * @param request
     * @param response
     * @return
     * @throws java.io.IOException
     */
    @Transactional
    public String importCust(String path, HttpServletRequest request, HttpServletResponse response) throws Exception {
        String filePath = path.substring(path.indexOf("/upload") + 7);
        User user = (User) request.getSession().getAttribute(ShmsConstant.LOGIN_USER_SESSION_NAME);
        if (user == null) {
            throw new NotAuthException("用户未授权");
        }
        String uploadPath = shmsProperties.getUploadPath();
        File file = new File(uploadPath + File.separator + filePath);
        boolean flag = false;
        String rule = "^1[358]\\d{9}$";
        String errorMsg = "";
        Workbook workbook = null;
        workbook = WorkbookFactory.create(file);
        Sheet sh = workbook.getSheetAt(0);//
        List<VipCheck> potentialCusts = this.buildCustWithAjaxUpload(sh);//
        if (potentialCusts != null && potentialCusts.size() > 0) {
            for (VipCheck vipCheck : potentialCusts) {
                //判读是否为有效数据

                if (vipCheck.isValid()) {
                    VipXxb vipXxb = vipCheck.getVipXxb();
                    User user1 = new User();

                    String firstReport = null;
                    VipXxb vipXxbxh = vipXxbService.getVipCardno(vipXxb.getCardno(), null);
                    if (vipXxbxh != null) {
                        vipXxb.setPackageName(vipXxbxh.getPackageName());
                        vipXxb.setStartTime(vipXxbxh.getStartTime());
                        vipXxb.setEndTime(vipXxbxh.getEndTime());
                        vipXxb.setPackagePrice(vipXxbxh.getPackagePrice());
                        vipXxb.setTjDate(vipXxbxh.getTjDate());
                        vipXxb.setProblem(vipXxbxh.getProblem());
                        vipXxb.setFgrDoctor(vipXxbxh.getFgrDoctor());
                        vipXxb.setMeetTime(vipXxbxh.getMeetTime());
                        vipXxb.setMeetType(vipXxbxh.getMeetType());
                        vipXxb.setZgDoor(vipXxbxh.getZgDoor());
                        vipXxb.setAsgender(vipXxbxh.getAsgender());
                        vipXxb.setCooperationDegree(vipXxbxh.getCooperationDegree());
                        vipXxb.setLastDate(vipXxbxh.getLastDate());
                        vipXxb.setClientNo(vipXxbxh.getClientNo());
                        vipXxb.setFirstReport(vipXxbxh.getFirstReport());
                        vipXxb.setLastReport(vipXxbxh.getLastReport());
                        firstReport = vipXxbxh.getFirstReport();
                    }
                    VipXxb vipXxbAmb = vipXxbService.getVipAmb(vipXxb.getCardno());
                    if (vipXxbAmb != null) {
                        vipXxb.setPromoteDoctor(vipXxbAmb.getPromoteDoctor());
                        vipXxb.setAmbId(vipXxbAmb.getAmbId());
                    }
                    VipXxb vipXxbTj = vipXxbService.getVipFirstReport(firstReport);
                    if (vipXxbTj == null) {
                        firstReport = vipXxbxh.getObligateB();
                        vipXxbTj = vipXxbService.getVipFirstReport(firstReport);
                        if (vipXxbTj != null) {
                            vipXxb.setMzId(vipXxbTj.getMzId());
                            vipXxb.setTjId(vipXxbTj.getTjId());
                            vipXxb.setProblem(vipXxbTj.getProblem());
                            vipXxb.setTjDate(vipXxbTj.getTjDate());
                        }
                    } else {
                        vipXxb.setMzId(vipXxbTj.getMzId());
                        vipXxb.setTjId(vipXxbTj.getTjId());
                        vipXxb.setProblem(vipXxbTj.getProblem());
                    }
                    vipXxb.setBox("1,2,3,4,5,6");
                    vipXxb.setObligateA("0");
                    vipXxbService.saveVipXxb(vipXxb, user1);
                    flag = true;
                } else {//无效数据
                    errorMsg += "第" + vipCheck.getRowNumber() + "行的姓名或录入日期为空，不能导入!";
                }
            }
        } else {
            return new JsonResponse<String>(JsonResponse.RETURN_FAILURE, "导入的文件无符合数据").toString();
        }

        if (flag) {
            if ("".equals(errorMsg)) {
                return new JsonResponse<String>(JsonResponse.RETURN_SUCCESS, "导入成功!" + errorMsg).toString();
            }
            return new JsonResponse<String>(JsonResponse.RETURN_SUCCESS, "部分导入成功!" + errorMsg).toString();
        } else {
            return new JsonResponse<String>(JsonResponse.RETURN_SUCCESS, "导入失败!" + errorMsg).toString();
        }

    }

    /**
     * 读取潜在客户导入的数据
     *
     * @param sh
     * @return
     * @throws Exception
     */
    @Transactional
    private List<VipCheck> buildCustWithAjaxUpload(Sheet sh) throws Exception {
        List<VipCheck> checks = new ArrayList<VipCheck>();
        int rows = sh.getLastRowNum() + 1 - sh.getFirstRowNum(); // 总行数
        List<CodeInfo> sexList = CodeInfoCache.getCodeInfo(CodeType.SEX);
        List<CodeInfo> cardList = CodeInfoCache.getCodeInfo(CodeType.CARD_TYPE);
        List<CodeInfo> boxList = CodeInfoCache.getCodeInfo(CodeType.BOX_TYPE);
        List<CodeInfo> coopeateList = CodeInfoCache.getCodeInfo(CodeType.COOPEATE_DEGREE);
        List<CodeInfo> interventionList = CodeInfoCache.getCodeInfo(CodeType.INTERVENTION_TYPE);
        List<CodeInfo> feelList = CodeInfoCache.getCodeInfo(CodeType.FEEL_TYPE);
        List<CodeInfo> dosageList = CodeInfoCache.getCodeInfo(CodeType.DOSAGE_SIZE);
        List<CodeInfo> vipList = CodeInfoCache.getCodeInfo(CodeType.VIP_RAND);
        List<CodeInfo> contactList = CodeInfoCache.getCodeInfo(CodeType.CONTACT_TIME_TYPE);
        List<CodeInfo> problemList = CodeInfoCache.getCodeInfo(CodeType.PROBLEM_TYPE);
        ExcelUtil excelUtil = new ExcelUtil();
        for (int i = 1; i < rows; i++) {
            boolean flag = true;//数据是否有效(客户姓名，手机号存在)
            boolean flag2 = false;//判断是否该行有数据
            VipXxb vipXxb = new VipXxb();
            VipCheck vipCheck = new VipCheck();
            vipCheck.setRowNumber(i + 1);
            vipCheck.setValid(true);
            String str = "";//下拉框
            // HSSFRow row = sh.getRow(i);//excel97-2003写法
            Row row = sh.getRow(i);
//            int cols = row.getLastCellNum() + 1 - row.getFirstCellNum(); // 该行的总列数
            Iterator cells = row.cellIterator();
            int j = 0;
            while (cells.hasNext()) {
                //判断行数据的客户姓名和电话是否为空，某一项为空则不执行循环
                if (StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, 0)) || StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, 1))) {
                    for (int r = 0; r <= 10; r++) {
                        if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, r))) {
                            if (!flag2) {
                                vipCheck.setValid(false);
                                flag2 = true;
                            }
                        }
                    }
                    flag = false;
                    break;
                }

//                HSSFCell cell = (HSSFCell) cells.next();//excel97-2003写法
                XSSFCell cell = (XSSFCell) cells.next();

                if (j == 0) {
                    //卡号
                    if (StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        throw new Exception("第【" + i + "】行数据，姓名不能为空！");
                    }
                    vipXxb.setClientName(ExcelUtil.getStringRowCellValue(row, j));
                }
                if (j == 1) {
                    //
                    if (StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        throw new Exception("第【" + i + "】行数据，身份证不能为空！");
                    }
                    vipXxb.setCardno(ExcelUtil.getStringRowCellValue(row, j));
                } else if (j == 2) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setVipId(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 3) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setMobile(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 4) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setVice(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 5) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setFirstTime(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 6) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setRequirements(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 7) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setContactName(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 8) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setContactPhone(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 9) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : contactList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setContactTime(c.getValue());
                            }
                        }
                    }
                } else if (j == 10) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setMedication(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 11) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : dosageList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setDrug(c.getValue());
                            }
                        }
                    }
                } else if (j == 12) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : feelList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setFeel(c.getValue());
                            }
                        }
                    }
                } else if (j == 13) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setAddress(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 14) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setDetailedAddress(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 15) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setNote(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 16) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setSyDoor(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 17) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : vipList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setProbability(c.getValue());
                            }
                        }
                    }

                } else if (j == 18) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : cardList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setTurnCard(c.getValue());
                            }
                        }
                    }

                } else if (j == 19) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : dosageList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setDosage(c.getValue());
                            }
                        }
                    }
                } else if (j == 20) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : feelList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setAfterFeeling(c.getValue());
                            }
                        }
                    }

                } else if (j == 21) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setTransferRecord(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 22) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setTransferAttention(ExcelUtil.getStringRowCellValue(row, j));
                    }
                }
                j++;
            }
            if (flag) {
//                 list.add(cust);
                vipCheck.setVipXxb(vipXxb);
                checks.add(vipCheck);
            } else {
//                vipCheck.setCustom(cust);
                if (flag2) {
                    checks.add(vipCheck);
                }
            }
        }
        return checks;
    }

    /**
     * 导出excel
     */
    @RequestMapping("/export")
    @ResponseBody
    public String export(VipXxbParams vipXxbParams, HttpServletRequest request, HttpServletResponse response) {
        try {
            vipXxbService.exportExcel(vipXxbParams, request, response);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return JsonResponse.error;
    }

}
